﻿using BackboneTest.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace BackboneTest.Domain
{
    public class UserRepository : SqLiteBaseRepository, IUserRepository
    {
        private static UserRepository _Instace; 

        public static UserRepository Instace
        {
            get {
                if (_Instace == null) _Instace = new UserRepository();
                return _Instace;
            }
        }
        public List<User> GetUsers()
        {
            List<User> users = new List<User>();
            using (var cnn = SimpleDbConnection())
            {
                cnn.Open();
                var command = cnn.CreateCommand();
                command.CommandText = @"select * from SysUser";

                var d = command.ExecuteReader();
                while (d.Read()) {
                    User user = new User();
                    user.ID = Convert.ToInt32(d["ID"]);
                    user.FirstName = d["FirstName"].ToString();
                    user.LastName = d["LastName"].ToString();
                    user.Age = Convert.ToInt32(d["Age"]);
                    users.Add(user);
                }

                return users;
            }
        }

        public void SaveUser(User user)
        {
            if (!File.Exists(DbFile))
            {
                CreateDataBase();
            }

            using (var cnn = SimpleDbConnection())
            {
                cnn.Open();
                var command = cnn.CreateCommand();
                if (user.ID == 0)
                {
                    command.CommandText = @"INSERT INTO SysUser 
                    ( FirstName, LastName, Age ) VALUES 
                    ( @FirstName, @LastName, @Age );
                    select last_insert_rowid()";
                }
                else
                {
                    command.CommandText = @"Update SysUser set 
                    FirstName = @FirstName,LastName = @LastName,Age = @Age
                    where ID = @ID;
                    select last_insert_rowid()";
                    command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@ID", user.ID));
                }

                command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@FirstName", user.FirstName));
                command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@LastName", user.LastName));
                command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@Age", user.Age));
           

                var d = command.ExecuteScalar();
            }
        }

        private static void CreateDataBase()
        {
            using (var cnn = SqLiteBaseRepository.SimpleDbConnection())
            {
                cnn.Open();
                var command =  cnn.CreateCommand();
                command.CommandText = @"create table SysUser
              (
                 ID                                  integer primary key AUTOINCREMENT,
                 FirstName                           varchar(100) null,
                 LastName                            varchar(100) null,
                 Age                         int null
              )";
                command.ExecuteNonQuery();
            }
        }

        public User GetUser(int id)
        {
            User user = new User();
            using (var cnn = SimpleDbConnection())
            {
                cnn.Open();
                var command = cnn.CreateCommand();
                command.CommandText = @"select * from SysUser where id = @id";
                command.Parameters.Add(new System.Data.SQLite.SQLiteParameter("@id", id));
                var d = command.ExecuteReader();
                while (d.Read())
                {
                    user.ID = Convert.ToInt32(d["ID"]);
                    user.FirstName = d["FirstName"].ToString();
                    user.LastName = d["LastName"].ToString();
                    user.Age = Convert.ToInt32(d["Age"]);
                }

                return user;
            }
        }
    }
}